package com.hh.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.hh.jdbc.JDBCDemo;

public class BRJBXXDAO {
	
	
	public JSONObject getBRJBXXList(){
		Connection con = null;
        PreparedStatement statement = null;
        ResultSet res = null;
        JSONObject obj = new JSONObject();
        JSONArray array1 = new JSONArray();
        JSONArray array2 = new JSONArray();
        try {
            Class.forName(JDBCDemo.cfn);
            con = DriverManager.getConnection(JDBCDemo.url,JDBCDemo.user,JDBCDemo.password);
            String sql = "select bqmc,count(mzhm) count from VIEW_ZY_BRJBXX_HIS group by bqmc";
            statement = con.prepareStatement(sql);
            res = statement.executeQuery();
            while(res.next()){
            	array1.add( res.getString("bqmc"));
            	JSONObject o = new JSONObject();
            	o.put("name", res.getString("bqmc"));
            	o.put("value", res.getInt("count"));
            	array2.add(o);
            }
            obj.put("bqmc", array1);
            obj.put("count", array2);
            
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }finally{
            try {
                if(res != null) res.close();
                if(statement != null) statement.close();
                if(con != null) con.close();
            } catch (Exception e2) {
                // TODO: handle exception
                e2.printStackTrace();
            }
        }
        return obj;
	}
	
	public JSONObject getTZYYList(String start,String end){
		Connection con = null;
        PreparedStatement statement = null;
        ResultSet res = null;
        JSONObject obj = new JSONObject();
        JSONArray array1 = new JSONArray();
        JSONArray array2 = new JSONArray();
        try {
            Class.forName(JDBCDemo.cfn);
            con = DriverManager.getConnection(JDBCDemo.url,JDBCDemo.user,JDBCDemo.password);
            String sql = " SELECT zd_jczd.zdmc,count(XH) AS RC FROM PD_ZTJLB,zd_jczd "+
        			" WHERE zd_jczd.sbxh=PD_ZTJLB.ZTYY ";
            if(start!=null&&!start.equals("")){
            	sql = sql + " and PD_ZTJLB.TZRQ>='"+start+"' "; 
            }
            if(end!=null&&!end.equals("")){
            	sql = sql + " and PD_ZTJLB.TZRQ<='"+end+"' "; 
            }
          
            sql = sql + " and ZD_JCZD.ZDDM=9980 AND ZD_JCZD.XTXH='001' "+
            			" AND ZD_JCZD.SBXH>0 and (sfgt=0 or sfgt is null) "+
            			" GROUP BY zd_jczd.zdmc order by rc ";
            statement = con.prepareStatement(sql);
            res = statement.executeQuery();
            while(res.next()){
            	array1.add( res.getString("zdmc"));
            	JSONObject o = new JSONObject();
            	o.put("name", res.getString("zdmc"));
            	o.put("value", res.getInt("RC"));
            	array2.add(o);
            }
            obj.put("bqmc", array1);
            obj.put("count", array2);
            
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }finally{
            try {
                if(res != null) res.close();
                if(statement != null) statement.close();
                if(con != null) con.close();
            } catch (Exception e2) {
                // TODO: handle exception
                e2.printStackTrace();
            }
        }
        return obj;
	}
}
